package com.mdsi.teacher;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mdsi.models.ConnectionManager;
import com.mdsi.models.Student;
import com.mdsi.models.Subject;
import com.mdsi.models.Teacher;
import com.mdsi.models.Room.TypeRoom;

/**
 * Servlet implementation class TeacherAbsence
 */
@WebServlet("/User/Teacher/Absences")
public class TeacherAbsence extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public TeacherAbsence() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Integer min = null ;
		Boolean present = false ;
		Boolean nonJustify = false ;
		TypeRoom typeRoom = null ;
		if (request.getParameter("min") == null && request.getParameter("present") == null) {
			response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
			return ;
		}
		if (request.getParameter("type") != null) {
			typeRoom = TypeRoom.valueOf(request.getParameter("type")) ;
		}
		if (request.getParameter("nonJustify") != null) {
			nonJustify = true ;
		}
		if (request.getParameter("min") == null) {
			present = true ;
		}
		else {
			try {
				min = Integer.parseInt(request.getParameter("min")) ;
			}
			catch (NumberFormatException nfe) {
				response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
				return ;
			}
		}
		try {
			Subject subject = null ;
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = null ;
			ResultSet result = null ;
			if (request.getParameter("subject") != null) {
				st = conn.prepareStatement("SELECT UNIQUE ID_SUBJECT, NAME FROM SUBJECT WHERE ID_SUBJECT = ?") ;
				st.setLong(1, Long.valueOf(request.getParameter("subject"))) ;
				result = st.executeQuery() ;
				if (!result.next()) {
					conn.close() ;
					response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
					return ;
				}
				subject = new Subject(result.getLong("ID_SUBJECT"), result.getString("NAME")) ;
			}
			Teacher teacher = (Teacher) request.getSession().getAttribute("user") ;
			String sqlRequest = "" ;
			if (min != null) {
				if (min < 0) {
					sqlRequest = "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
							+ "FROM STUDENT S, (SELECT COUNT (*) AS NB_ABS, A.STUDENT.ID_STUDENT AS STUDENT "
								+ "FROM ABSENCE A "
								+ "WHERE A.LESSON.TEACHER.ID_TEACHER = " + teacher.getId() + " "
									+ ((subject != null) ? "AND A.LESSON.SUBJECT.ID_SUBJECT = " + subject.getId() + " " : "")
									+ ((typeRoom != null) ? "AND A.LESSON.ROOM.TYPE_ROOM = '" + String.valueOf(typeRoom) + "' " : "")
									+ ((nonJustify != null && nonJustify) ? "AND A.JUSTIFY = 'N' " : "")
								+ "GROUP BY (A.STUDENT)) CA "
							+ "WHERE CA.STUDENT = S.ID_STUDENT "
							+ "AND CA.NB_ABS = (SELECT COUNT (*) FROM LESSON L, REGISTER R WHERE L.TEACHER.ID_TEACHER = " + teacher.getId()
								+ ((subject != null) ? "AND L.ID_SUBJECT = " + subject.getId() + " " : 
									"AND R.STUDENT.ID_STUDENT = S.ID_STUDENT AND R.SUBJECT.ID_SUBJECT = L.SUBJECT.ID_SUBJECT ")
								+ ") " 
							+ "ORDER BY S.FIRSTNAME, S.LASTNAME" ;
				}
				else {
					sqlRequest = "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
							+ "FROM STUDENT S, (SELECT COUNT (*) AS NB_ABS, A.STUDENT.ID_STUDENT AS STUDENT "
								+ "FROM ABSENCE A "
								+ "WHERE A.LESSON.TEACHER.ID_TEACHER = " + teacher.getId() + " "
								+ ((subject != null) ? "AND A.LESSON.SUBJECT.ID_SUBJECT = " + subject.getId() + " " : "")
								+ ((typeRoom != null) ? "AND A.LESSON.ROOM.TYPE_ROOM = '" + String.valueOf(typeRoom) + "' " : "")
								+ ((nonJustify != null && nonJustify) ? "AND A.JUSTIFY = 'N' " : "")
								+ "GROUP BY (A.STUDENT)) CA "
							+ "WHERE CA.STUDENT = S.ID_STUDENT "
							+ "AND CA.NB_ABS >= " + min + " "
							+ "ORDER BY S.FIRSTNAME, S.LASTNAME" ;
				}
			}
			else if (present) {
				sqlRequest = "SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
						+ "FROM STUDENT S, REGISTER R "
						+ "WHERE R.STUDENT = REF(S) "
						+ ((subject != null) ? "AND R.SUBJECT.ID_SUBJECT = " + subject.getId() + " " : "")
						+ "AND S.ID_STUDENT NOT IN (SELECT DISTINCT A.STUDENT.ID_STUDENT "
							+ "FROM ABSENCE A "
							+ "WHERE A.LESSON.TEACHER.ID_TEACHER = " + teacher.getId() + " "
							+ ((subject != null) ? "AND A.LESSON.SUBJECT.ID_SUBJECT = " + subject.getId() + " " : "")
							+ ")"
						+ "ORDER BY S.FIRSTNAME, S.LASTNAME" ;
			}
			st = conn
					.prepareStatement(sqlRequest) ;
			result = st.executeQuery() ;
			ArrayList <Student> students = new ArrayList <Student> () ;
			while (result.next()) {
				Calendar birthdate = GregorianCalendar.getInstance() ;
				birthdate.setTime(result.getDate("BIRTHDATE")) ;
				students.add(new Student(
					result.getLong("ID_STUDENT"), 
					result.getString("FIRSTNAME"), 
					result.getString("LASTNAME"),
					result.getString("EMAIL"), 
					result.getLong("NUM"), 
					birthdate, 
					result.getString("NATIONALITY"))) ;
			}
			
			conn.close () ;
			getServletContext().setAttribute("typeRoom", typeRoom) ;
			getServletContext().setAttribute("min", min) ;
			getServletContext().setAttribute("subject", subject) ;
			getServletContext().setAttribute("students", students) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		this.getServletContext().getRequestDispatcher("/WEB-INF/teacher/absences.jsp").forward(request, response);			
		
	}

}
